package com.ph.irp.base.verifyproject.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.verifyproject.dao.PBaseVerifyprojectCustomerDao;
import com.ph.irp.model.PBaseVerifyprojectCustomer;
import com.ph.util.DBUtil;

public class PBaseVerifyprojectCustomerDaoImpl implements
		PBaseVerifyprojectCustomerDao {
	private ComboPooledDataSource dataSource;
	
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addCustomer(PBaseVerifyprojectCustomer customer) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_verifyproject_customer(CustomerCode,CustomerName");
		if(customer.getCustomerBrief() != null && !customer.getCustomerBrief().equals("")){
			sb.append(",CustomerBrief");
		}
		if(customer.getAddress() != null && !customer.getAddress().equals("")){
			sb.append(",Address");
		}
		if(customer.getPBaseAssistantmaterialAreaId() != 0){
			sb.append(",AreaID");
		}
		if(customer.getPBaseAssistantmaterialIndustryId() != 0){
			sb.append(",IndustryID");
		}
		if(customer.getLinkman() != null && !customer.getLinkman().equals("")){
			sb.append(",Linkman");
		}
		if(customer.getPhone() != null && !customer.getPhone().equals("")){
			sb.append(",Phone");
		}
		if(customer.getFax() != null && !customer.getFax().equals("")){
			sb.append(",Fax");
		}
		sb.append(",CreditNum");
		if(customer.getPBaseAssistantmaterialBalancedeadlineId() != 0){
			sb.append(",BalanceDeadLineID");
		}
		if(customer.getPost() != null && !customer.getPost().equals("")){
			sb.append(",Post");
		}
		if(customer.getMailAddress() != null && !customer.getMailAddress().equals("")){
			sb.append(",mailAddress");
		}
		if(customer.getOpenAccountBank() != null && !customer.getOpenAccountBank().equals("")){
			sb.append(",openAccountBank");
		}
		if(customer.getBankAccount() != null && !customer.getBankAccount().equals("")){
			sb.append(",BankAccount");
		}
		if(customer.getTaxRegisterNo() != null && !customer.getTaxRegisterNo().equals("")){
			sb.append(",TaxRegisterNO");
		}
		sb.append(",IncreaseTaxPercent");
		if(customer.getCity() != null && !customer.getCity().equals("")){
			sb.append(",city");
		}
		if(customer.getProvince() != null && !customer.getProvince().equals("")){
			sb.append(",province");
		}
		if(customer.getCountry() != null && !customer.getCountry().equals("")){
			sb.append(",country");
		}
		if(customer.getWebSite() != null && !customer.getWebSite().equals("")){
			sb.append(",WebSite");
		}
		if(customer.getResponsibleMan() != null && !customer.getResponsibleMan().equals("")){
			sb.append(",responsibleMan");
		}
		if(customer.getPBaseAssistantmaterialCustomercategoryId() != 0){
			sb.append(",CustomerCategoryID");
		}
		if(customer.getSalesMethod() != 0){
			sb.append(",salesMethod");
		}
		if(customer.getPBaseVerifyprojectDepartmentId() != 0){
			sb.append(",DepartmentID");
		}
		if(customer.getPBaseVerifyprojectCandiateId() != 0){
			sb.append(",OwnerID");
		}
		sb.append(") values(");
		sb.append("'");
		sb.append(customer.getCustomerCode());
		sb.append("'");
		sb.append(",'");
		sb.append(customer.getCustomerName());
		sb.append("'");
		if(customer.getCustomerBrief() != null && !customer.getCustomerBrief().equals("")){
			sb.append(",'").append(customer.getCustomerBrief().trim()).append("'");
		}
		if(customer.getAddress() != null && !customer.getAddress().equals("")){
			sb.append(",'").append(customer.getAddress().trim()).append("'");
		}
		if(customer.getPBaseAssistantmaterialAreaId() != 0){
			sb.append(",").append(customer.getPBaseAssistantmaterialAreaId());
		}
		if(customer.getPBaseAssistantmaterialIndustryId() != 0){
			sb.append(",").append(customer.getPBaseAssistantmaterialIndustryId());
		}
		if(customer.getLinkman() != null && !customer.getLinkman().equals("")){
			sb.append(",'").append(customer.getLinkman().trim()).append("'");
		}
		if(customer.getPhone() != null && !customer.getPhone().equals("")){
			sb.append(",'").append(customer.getPhone().trim()).append("'");
		}
		if(customer.getFax() != null && !customer.getFax().equals("")){
			sb.append(",'").append(customer.getFax().trim()).append("'");
		}
		sb.append(",").append(customer.getCreditNum());
		if(customer.getPBaseAssistantmaterialBalancedeadlineId() != 0){
			sb.append(",").append(customer.getPBaseAssistantmaterialBalancedeadlineId());
		}
		if(customer.getPost() != null && !customer.getPost().equals("")){
			sb.append(",'").append(customer.getPost().trim()).append("'");
		}
		if(customer.getMailAddress() != null && !customer.getMailAddress().equals("")){
			sb.append(",'").append(customer.getMailAddress().trim()).append("'");
		}
		if(customer.getOpenAccountBank() != null && !customer.getOpenAccountBank().equals("")){
			sb.append(",'").append(customer.getOpenAccountBank().trim()).append("'");
		}
		if(customer.getBankAccount() != null && !customer.getBankAccount().equals("")){
			sb.append(",'").append(customer.getBankAccount().trim()).append("'");
		}
		if(customer.getTaxRegisterNo() != null && !customer.getTaxRegisterNo().equals("")){
			sb.append(",'").append(customer.getTaxRegisterNo().trim()).append("'");
		}
		sb.append(",").append(customer.getIncreaseTaxPercent());
		if(customer.getCity() != null && !customer.getCity().equals("")){
			sb.append(",'").append(customer.getCity().trim()).append("'");
		}
		if(customer.getProvince() != null && !customer.getProvince().equals("")){
			sb.append(",'").append(customer.getProvince().trim()).append("'");
		}
		if(customer.getCountry() != null && !customer.getCountry().equals("")){
			sb.append(",'").append(customer.getCountry().trim()).append("'");
		}
		if(customer.getWebSite() != null && !customer.getWebSite().equals("")){
			sb.append(",'").append(customer.getWebSite().trim()).append("'");
		}
		if(customer.getResponsibleMan() != null && !customer.getResponsibleMan().equals("")){
			sb.append(",'").append(customer.getResponsibleMan().trim()).append("'");
		}
		if(customer.getPBaseAssistantmaterialCustomercategoryId() != 0){
			sb.append(",").append(customer.getPBaseAssistantmaterialCustomercategoryId());
		}
		if(customer.getSalesMethod() != 0){
			sb.append(",").append(customer.getSalesMethod());
		}
		if(customer.getPBaseVerifyprojectDepartmentId() != 0){
			sb.append(",").append(customer.getPBaseVerifyprojectDepartmentId());
		}
		if(customer.getPBaseVerifyprojectCandiateId() != 0){
			sb.append(",").append(customer.getPBaseVerifyprojectCandiateId());
		}
		sb.append(")");
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		}catch(Exception e){
		   e.printStackTrace();
		   throw new Exception(e.getMessage());
		}finally{
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void deleteCustomer(int customerId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "delete from p_base_verifyproject_customer where CustomerID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, customerId);
			pStmt.execute();
		}catch(Exception e){
			   e.printStackTrace();
			   throw new Exception(e.getMessage());
			}
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public PBaseVerifyprojectCustomer getCustomerById(int customerId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseVerifyprojectCustomer customer = null;
		String sql = "select * from p_base_verifyproject_customer where CustomerID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, customerId);
			rs = pStmt.executeQuery();
			if(rs.next()){
				customer = new PBaseVerifyprojectCustomer();
				customer.setCustomerId(rs.getInt("CustomerID"));
				customer.setCustomerCode(rs.getString("CustomerCode"));
				customer.setCustomerName(rs.getString("CustomerName"));
				customer.setCustomerBrief(rs.getString("CustomerBrief"));
				customer.setAddress(rs.getString("Address"));
				customer.setPBaseAssistantmaterialAreaId(rs.getInt("AreaID"));
				customer.setPBaseAssistantmaterialIndustryId(rs.getInt("IndustryID"));
				customer.setLinkman(rs.getString("Linkman"));
				customer.setPhone(rs.getString("Phone"));
				customer.setFax(rs.getString("Fax"));
				customer.setCreditNum(rs.getFloat("CreditNum"));
				customer.setPBaseAssistantmaterialBalancedeadlineId(rs.getInt("BalanceDeadLineID"));
				customer.setPost(rs.getString("Post"));;
				customer.setMailAddress(rs.getString("mailAddress"));;
				customer.setOpenAccountBank(rs.getString("openAccountBank"));
				customer.setBankAccount(rs.getString("BankAccount"));
				customer.setTaxRegisterNo(rs.getString("TaxRegisterNO"));
				customer.setIncreaseTaxPercent(rs.getInt("IncreaseTaxPercent"));
				customer.setCity(rs.getString("city"));
				customer.setProvince(rs.getString("province"));
				customer.setCountry(rs.getString("country"));
				customer.setWebSite(rs.getString("WebSite"));
				customer.setResponsibleMan(rs.getString("responsibleMan"));
				customer.setPBaseAssistantmaterialCustomercategoryId(rs.getInt("CustomerCategoryID"));
				customer.setSalesMethod(rs.getInt("salesMethod"));
				customer.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				customer.setPBaseVerifyprojectCandiateId(rs.getInt("OwnerID"));
			}
		}catch(Exception e){
			   e.printStackTrace();
			   throw new Exception(e.getMessage());
			}
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return customer;
	}

	@Override
	public List<PBaseVerifyprojectCustomer> getCustomerList(
			PBaseVerifyprojectCustomer customer) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectCustomer> list = new ArrayList<PBaseVerifyprojectCustomer>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_customer where 1=1");
		if(customer!=null){
			if(customer.getCustomerCode() != null && !customer.getCustomerCode().equals("")){
				sb.append(" and CustomerCode like '%");
				sb.append(customer.getCustomerCode().trim());
				sb.append("%'");
			}
			if(customer.getCustomerName() != null && !customer.getCustomerName().equals("")){
				sb.append(" and CustomerName like '%");
				sb.append(customer.getCustomerName().trim());
				sb.append("%'");
			}
			if(customer.getPBaseAssistantmaterialAreaId() != 0){
				sb.append(" and AreaID=");
				sb.append(customer.getPBaseAssistantmaterialAreaId());
			}
			if(customer.getAddress() != null && !customer.getAddress().equals("")){
				sb.append(" and Address like '%");
				sb.append(customer.getAddress().trim());
				sb.append("%'");
			}
		}
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectCustomer pbvc = new PBaseVerifyprojectCustomer();
				pbvc.setCustomerId(rs.getInt("CustomerID"));
				pbvc.setCustomerCode(rs.getString("CustomerCode"));
				pbvc.setCustomerName(rs.getString("CustomerName"));
				pbvc.setCustomerBrief(rs.getString("CustomerBrief"));
				pbvc.setAddress(rs.getString("Address"));
				pbvc.setPBaseAssistantmaterialAreaId(rs.getInt("AreaID"));
				pbvc.setPBaseAssistantmaterialIndustryId(rs.getInt("IndustryID"));
				pbvc.setLinkman(rs.getString("Linkman"));
				pbvc.setPhone(rs.getString("Phone"));
				pbvc.setFax(rs.getString("Fax"));
				pbvc.setCreditNum(rs.getFloat("CreditNum"));
				pbvc.setPBaseAssistantmaterialBalancedeadlineId(rs.getInt("BalanceDeadLineID"));
				pbvc.setPost(rs.getString("Post"));
				pbvc.setMailAddress(rs.getString("mailAddress"));
				pbvc.setOpenAccountBank(rs.getString("openAccountBank"));
				pbvc.setBankAccount(rs.getString("BankAccount"));
				pbvc.setTaxRegisterNo(rs.getString("TaxRegisterNO"));
				pbvc.setIncreaseTaxPercent(rs.getInt("IncreaseTaxPercent"));
				pbvc.setCity(rs.getString("city"));
				pbvc.setProvince(rs.getString("province"));
				pbvc.setCountry(rs.getString("country"));
				pbvc.setWebSite(rs.getString("WebSite"));
				pbvc.setResponsibleMan(rs.getString("responsibleMan"));
				pbvc.setPBaseAssistantmaterialCustomercategoryId(rs.getInt("CustomerCategoryID"));
				pbvc.setSalesMethod(rs.getInt("salesMethod"));
				pbvc.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				pbvc.setPBaseVerifyprojectCandiateId(rs.getInt("OwnerID"));
				list.add(pbvc);
			}
		}catch(Exception e){
			   e.printStackTrace();
			   throw new Exception(e.getMessage());
			}
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@Override
	public boolean isSame(String customerCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "select * from p_base_verifyproject_customer where CustomerCode='"+customerCode.trim()+"'";
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if(rs.next()){
				return true;
			}else{
				return false;
			}
		}catch(Exception e){
			   e.printStackTrace();
			   throw new Exception(e.getMessage());
			}
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void updateCustomer(PBaseVerifyprojectCustomer customer)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
			sb.append("update p_base_verifyproject_customer set CustomerCode='");
			sb.append(customer.getCustomerCode()).append("',CustomerName='");
			sb.append(customer.getCustomerName()).append("'");
	        sb.append(",CustomerBrief='").append(customer.getCustomerBrief().trim()).append("'");
			sb.append(",Address='").append(customer.getAddress().trim()).append("'");
			if(customer.getPBaseAssistantmaterialAreaId()!=0){
			 sb.append(",AreaID='").append(customer.getPBaseAssistantmaterialAreaId()).append("'");
			}
			if(customer.getPBaseAssistantmaterialIndustryId()!=0){
			 sb.append(",IndustryID='").append(customer.getPBaseAssistantmaterialIndustryId()).append("'");
			}
			sb.append(",Linkman='").append(customer.getLinkman().trim()).append("'");
			sb.append(",Phone='").append(customer.getPhone().trim()).append("'");
			sb.append(",Fax='").append(customer.getFax().trim()).append("'");
		    sb.append(",CreditNum='").append(customer.getCreditNum()).append("'");
		    if(customer.getPBaseAssistantmaterialBalancedeadlineId()!=0){
			  sb.append(",BalanceDeadLineID='").append(customer.getPBaseAssistantmaterialBalancedeadlineId()).append("'");
		    }
			sb.append(",Post='").append(customer.getPost().trim()).append("'");
			sb.append(",mailAddress='").append(customer.getMailAddress().trim()).append("'");
			sb.append(",openAccountBank='").append(customer.getOpenAccountBank().trim()).append("'");
			sb.append(",BankAccount='").append(customer.getBankAccount().trim()).append("'");
			sb.append(",TaxRegisterNO='").append(customer.getTaxRegisterNo().trim()).append("'");
		    sb.append(",IncreaseTaxPercent= '").append(customer.getIncreaseTaxPercent()).append("'");
			sb.append(",city='").append(customer.getCity().trim()).append("'");
			sb.append(",province='").append(customer.getProvince().trim()).append("'");
			sb.append(",country='").append(customer.getCountry().trim()).append("'");
			sb.append(",WebSite='").append(customer.getWebSite().trim()).append("'");
			sb.append(",responsibleMan='").append(customer.getResponsibleMan().trim()).append("'");
			if(customer.getPBaseAssistantmaterialCustomercategoryId()!=0){
			 sb.append(",CustomerCategoryID= '").append(customer.getPBaseAssistantmaterialCustomercategoryId()).append("'");
			}
			 sb.append(",salesMethod= '").append(customer.getSalesMethod()).append("'");
			if(customer.getPBaseVerifyprojectDepartmentId()!=0){		
			 sb.append(",DepartmentID= '").append(customer.getPBaseVerifyprojectDepartmentId()).append("'");
			}
			if(customer.getPBaseVerifyprojectCandiateId()!=0){
			 sb.append(",OwnerID= '").append(customer.getPBaseVerifyprojectCandiateId()).append("'");
			}
		    sb.append(" where CustomerID= '").append(customer.getCustomerId()).append("'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		}catch(Exception e){
			   e.printStackTrace();
			   throw new Exception(e.getMessage());
			}
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

}
